IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N'[dbo].[sp_CongNo_KHTheoKhuVucNoCtyTrongKhoangNgayTheoNgoaiTe]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_CongNo_KHTheoKhuVucNoCtyTrongKhoangNgayTheoNgoaiTe]
GO


CREATE PROC [sp_CongNo_KHTheoKhuVucNoCtyTrongKhoangNgayTheoNgoaiTe]
(
	@Ma_chi_nhanh		INT,
	@Ma_khu_vuc			INT,
	@Ma_ngoai_te		INT,
	@Tu_ngay			DATETIME,
	@Den_ngay			DATETIME	
)
AS
BEGIN
	DECLARE @Tu_ngay_convert	NVARCHAR(10)
	DECLARE @Den_ngay_convert	NVARCHAR(10)

	SET @Tu_ngay_convert = CONVERT(NVARCHAR, DAY(@Tu_ngay)) + '/' + CONVERT(NVARCHAR, MONTH(@Tu_ngay)) + '/' + CONVERT(NVARCHAR, YEAR(@Tu_ngay))
	SET @Den_ngay_convert = CONVERT(NVARCHAR, DAY(@Den_ngay)) + '/' + CONVERT(NVARCHAR, MONTH(@Den_ngay)) + '/' + CONVERT(NVARCHAR, YEAR(@Den_ngay))

	SELECT A.Ten AS 'Ten_KH',
		B.Thanh_tien AS 'Cong_no',
		B.Ty_gia AS 'Ty_gia',
		-- tong tien no cua KH tu @Tu_nay den @Den_ngay
		SUM(CASE 
				WHEN C.Loai_nhap_xuat = 0 AND CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) BETWEEN @Tu_ngay AND @Den_ngay 
				THEN C.Tien_no 
				ELSE 0 END) AS 'Tong_tien_no_1',
		-- tong tien no cua KH tu @Den_ngay den ngay cuoi cung ma KH mua hang
		SUM(CASE 
				WHEN C.Loai_nhap_xuat = 0 AND CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) > @Den_ngay 
				THEN C.Tien_no 
				ELSE 0 END) AS 'Tong_tien_no_2'
	FROM KhachHang AS A INNER JOIN CongNo AS B ON A.id = B.Ma_khach_hang
		INNER JOIN PhieuNhapXuat AS C ON C.Ma_khach_hang = A.id
		INNER JOIN NgoaiTe AS D ON D.id = B.Ma_ngoai_te
		INNER JOIN ThanhVien AS E ON E.id = C.Ma_nhan_vien	
	WHERE E.Ma_chi_nhanh = @Ma_chi_nhanh 
		AND A.Ma_khu_vuc = @Ma_khu_vuc 
		AND D.id = @Ma_ngoai_te
	GROUP BY A.Ten, B.Thanh_tien, B.Ty_gia
END


